﻿using GPS.MODELS;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using GPS.DataAccessLayer;
using AutoMapper;
using PagedList;
using GPS.ENTITYS;
using GPS.COMMON;
using System.IO;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Oracle.ManagedDataAccess.Client;
using System.Data;
using Microsoft.AspNet.Identity;
using NPOI.HSSF.UserModel;
using OfficeOpenXml;
using System.Web.UI.WebControls;
using System.Web.UI;

namespace GPS.Controllers.GPS_BaoCao
{
    public class GPS_CB_GV_DUP_CMTController : Controller
    {
        #region onnit
        DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;
        public GPS.MODELS.ApplicationUser getCurrentUser()
        {
            ApplicationDbContext dbUser = new ApplicationDbContext();
            string userId = User.Identity.GetUserId();
            ApplicationUser _currentUser = dbUser.Users.Find(userId);
            return _currentUser;
        }
        public void initialCategorySearchAction(ViewReportModelGPS_CB_GV_DUP_CMT SearchModel)
        {
            string DVGTVT_MA = this.getCurrentUser().MA_TINHTP;
            ViewBag.checkuser = DVGTVT_MA;
            //string getdvgtvt_Ma = GPS.DAL.Check_User.CheckUser(this.getCurrentUser().MA_TINHTP, SearchModel.pDGV_SOCMT);
            //SearchModel.pDGV_SOCMT = getdvgtvt_Ma;
        }
        #endregion

        #region Index
        public ActionResult Index(ViewReportModelGPS_CB_GV_DUP_CMT SearchModel, int? currentPage)
        {
            initialCategorySearchAction(SearchModel);
            try
            {
               
                List<RepostModelGPS_CB_GV_DUP_CMT> model = new List<RepostModelGPS_CB_GV_DUP_CMT>();
                if (!string.IsNullOrEmpty(SearchModel.pDGV_SOCMT) && SearchModel.ExportButton == "Search")
                {
                    var entities = Getdata(SearchModel);
                    Int16 icount = 1;
                    foreach (var currentEntity in model)
                    {
                        currentEntity.STT = icount;
                        icount++;
                    }
                    ViewBag.TotalRow = model.Count();
                }
              

                if (SearchModel.ExportButton == "Xuất Excel")
                {
                    model = Getdata(SearchModel);
                    Int16 icount = 1;
                    //foreach (var currentEntity in model)
                    //{
                    //    currentEntity.STT = icount;
                    //    icount++;
                    //}
                    //ViewBag.TotalRow = model.Count();

                    FileInfo template = new FileInfo(Server.MapPath(@"\Templates\GPS_Excel\CB_GiaoVienTrungCMTND.xlsx"));
                    FileInfo newFile = new FileInfo(Server.MapPath(@"\Templates\GPS_Excel\CB_DonViHetHanGiayPhepNEW.xlsx"));

                    // Using the template to create the newFile...
                    using (OfficeOpenXml.ExcelPackage excelPackage = new OfficeOpenXml.ExcelPackage(newFile, template))
                    {
                        // Getting the complete workbook...
                        ExcelWorkbook myWorkbook = excelPackage.Workbook;
                        // Getting the worksheet by its name...
                        ExcelWorksheet myWorksheet = myWorkbook.Worksheets["Sheet1"];
                        int recordIndex = 4;
                        foreach (var item in model)
                        {
                            myWorksheet.Cells[recordIndex, 1].Value = item.STT.ToString();
                            myWorksheet.Cells[recordIndex, 2].Value = item.DVGTVT_TEN;
                            myWorksheet.Cells[recordIndex, 3].Value = item.GV_HOTEN;
                            myWorksheet.Cells[recordIndex, 4].Value = item.GV_NGAYSINH == null ? "" : item.GV_NGAYSINH.ToString();
                            myWorksheet.Cells[recordIndex, 5].Value = item.GV_SOCMT == null ? "" : item.GV_SOCMT;
                            myWorksheet.Cells[recordIndex, 6].Value = item.GV_GIOITINH == null ? "" : item.GV_GIOITINH;
                            myWorksheet.Cells[recordIndex, 7].Value = item.GV_TEL == null ? "" : item.GV_TEL;
                            recordIndex++;
                        }
                        excelPackage.Save();
                    }
                    string contentType = string.Empty;
                    string CurrentFileName = Server.MapPath(@"\Templates\GPS_Excel\CB_DonViHetHanGiayPhepNEW.xlsx");
                    if (CurrentFileName.Contains(".xlsx"))
                    {
                        contentType = "application/msexcel";
                    }
                    return File(CurrentFileName, contentType, string.Format("CanhBao_GiaoVienTrungCMTND_{0}.xlsx", DateTime.Now.ToShortDateString().Replace("/", "_")));
                }
                else
                {
                    var entities = Getdata(SearchModel);
                    Int16 icount = 1;
                    foreach (var currentEntity in model)
                    {
                        currentEntity.STT = icount;
                        icount++;
                    }
                    ViewBag.TotalRow = entities.Count();
                    var pageIndex = SearchModel.Page ?? 1;
                    SearchModel.SearchResults = entities.ToPagedList(pageIndex, GPS.COMMON.Constants.PageSize);
                    return View(SearchModel);
                }
            }
            catch (Exception ex)
            {
                TempData["Message"] = ex.ToString();
                return RedirectToAction("Index");
            }
        }
        #endregion
        public List<RepostModelGPS_CB_GV_DUP_CMT> Getdata(ViewReportModelGPS_CB_GV_DUP_CMT SearchModel)
        {
            var _pDGV_SOCMT = new OracleParameter("pDCP_GV_DVGTVT_MA", OracleDbType.Varchar2, (SearchModel.pDGV_SOCMT == null ? DBNull.Value : (object)SearchModel.pDGV_SOCMT), ParameterDirection.Input);
            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var entities = _db.DbContext.Database.SqlQuery<RepostModelGPS_CB_GV_DUP_CMT>("BEGIN GPS_CANHBAO.GPS_CANHBAO_GV_DUP_CMT(:pDGV_SOCMT, :pRET); end;",_pDGV_SOCMT, _pRET).ToList();
            return entities;
        }
    }
}